package com.starbaba.user.datainput.service;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadFactory;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpSession;

import org.apache.commons.lang3.StringUtils;
import org.apache.http.message.BasicNameValuePair;
import org.springframework.stereotype.Component;

import com.base.common.database.BaseDao;
import com.base.common.exception.AppException;
import com.base.common.log.LogService;
import com.google.gson.JsonObject;
import com.starbaba.core.web.page.PageResponse;
import com.starbaba.model.dictionary.Constant;
import com.starbaba.model.dictionary.IServiceType;
import com.starbaba.model.utils.HttpConnectionUtils;
import com.starbaba.model.utils.JSONUtils;
import com.starbaba.user.datainput.model.UserDataInputModel;
import com.starbaba.user.setinfo.model.UserInfoSetModel;
import com.starbaba.util.ExcelOperateUtil;
import com.starbaba.util.PhoneOrderUtil;
import com.tuanzi.server.loan_base.util.PhoneNumNideUtil;

@SuppressWarnings({"rawtypes", "unchecked", "deprecation"})
@Component
public class UserDataInputService {

	private final static String SESSION_LIST_KEY = "USER_DATA_INPUT_TEMP_LIST";
	private final static String SESSION_FILE_KEY = "USER_DATA_IS_INPUT_TEMP";
	
	private static BaseDao dao = new BaseDao(Constant.POOL_LIFE);
	
	public void showPage(Map<String, Object> paramsMap) throws Exception {
		
		List<UserDataInputModel> prdList = dao.list("select b.name prd_name, a.prd_id prd_id from input_userdata a, comm_product_prdid b where a.prd_id = b.prdid group by a.prd_id", UserDataInputModel.class);
		List<UserDataInputModel> channelList = dao.list("select a.channel, c.channel_name from input_userdata a, comm_product_channel c where a.channel = c.channel_code group by a.channel", UserDataInputModel.class);
		List<UserDataInputModel> activityList = dao.list("select activity_name from input_userdata group by activity_name", UserDataInputModel.class);
		
		paramsMap.put("prdList", prdList);
		paramsMap.put("channelList", channelList);
		paramsMap.put("activityList", activityList);
		
	}
	
	public List getList(Integer start, Integer length, PageResponse pageResponse, Map<String, Object> paramsMap, HttpSession session) throws Exception {
		
		List<UserDataInputModel> dataList = (List<UserDataInputModel>) session.getAttribute(SESSION_LIST_KEY);
		if (null == dataList) {
			dataList = getDBList(start, length, pageResponse, paramsMap);
		} 
		return dataList;
	}

	/**
	 * Excel表格数据上传session
	 * @param file
	 * @param paramsMap
	 * @param session
	 * @throws Exception
	 */
	public void uploadFileToTemp(File file, Map<String, Object> paramsMap, HttpSession session) throws Exception {
		
		String getActivity_name = "select id activity_id, name activity_name from loan_product_info ";
		String getUserInfo = "select a.product_id prd_id, b.name prd_name,  c.channel_name channel_name,"
				+ " a.channel channel from comm_user a left join comm_product_prdid b on a.product_id = b.prdid "
				+ "left join comm_product_channel c on a.channel = c.channel_code where a.mobnum = ? limit 1 ";
		
		Map<String, String> actInfoMap = new HashMap<String, String>();
		List<UserDataInputModel> actInfoList = dao.list(getActivity_name, UserDataInputModel.class);
		for (UserDataInputModel model : actInfoList) {
			actInfoMap.put(model.getActivity_id(), model.getActivity_name());
		}
		
		String fileSuffix = (String) paramsMap.get("suffix");
		String operator = (String) paramsMap.get("operator");
		String ctime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
		
		if (file.exists()) {
			String fileName = file.getAbsolutePath();
			fileName = fileName.replace(".tmp", "." + fileSuffix.toLowerCase());
			file.renameTo(new File(fileName));
			file = new File(fileName);	
		}
		
		ExcelOperateUtil poi = new ExcelOperateUtil();
		String[][] result = poi.getData(file);
		
		// Excel表格的第几行
		int rows = 1;
		
		List<UserDataInputModel> fileList = new ArrayList<UserDataInputModel>();
		for (String[] row : result) {
			if (rows == 1) {
				rows++;
				continue;
			}
			
			if (!isInteger(row[0])) {
				rows++;
				continue;
			}
			
			String activity_id = row[0].substring(0, row[0].indexOf("."));
			String phone = null;
			
			if (isPhone(row[1])) {
				phone = row[1].substring(0, row[1].indexOf("."));
				
			} else if (row[1].length() == 11) {
				// 是模糊的手机号码
				 phone = handleVaguePhone(activity_id, row[1]);
				
			} else {
				// 不是手机号码
				rows++;
				continue;
			}
			
			String register_date = patternDate(row[2]);
			String apply_date = patternDate(row[3]);
			String loan_date = patternDate(row[4]);
			
			String loan_money = isNumber(row[5]) ? row[5] : null;
			String settlement_money = isNumber(row[6]) ? row[6] : null;
			
			// TODO 备用参数的处理
			String param1 = row[7];
			String param2 = row[8];
			String param3 = row[9];
			String param4 = row[10];
			
			String activity_name = "";
			String prd_name = "";
			String channel_name = "";
			String prd_id = "";
			String channel = "";

			if (StringUtils.isNotEmpty(actInfoMap.get(activity_id))) {
				activity_name = actInfoMap.get(activity_id);
			}
			
			List<UserDataInputModel> userInfoList = dao.list(getUserInfo, UserDataInputModel.class, phone);
			if (userInfoList != null && userInfoList.size() > 0) {
				UserDataInputModel userInfo = userInfoList.get(0);
				if (userInfo != null) {
					if (StringUtils.isNotEmpty(userInfo.getPrd_name())) {
						prd_name = userInfo.getPrd_name();
						prd_id = userInfo.getPrd_id();
					} else {
						prd_name = userInfo.getPrd_id();
						prd_id = userInfo.getPrd_id();
					}
					if (StringUtils.isNotEmpty(userInfo.getChannel_name())) {
						channel_name = userInfo.getChannel_name();
						channel = userInfo.getChannel();
					} else {
						channel_name = userInfo.getChannel();
						channel = userInfo.getChannel();
					}
				}
				
			}
			UserDataInputModel record = new UserDataInputModel(prd_id, channel,
					activity_id, activity_name, phone, register_date,
					apply_date, loan_date, loan_money, settlement_money, ctime,
					operator, param1, param2, param3, param4, prd_name,
					channel_name);
			fileList.add(record);
			rows++;
		}

		// 记录对应在集合中的位置
		int index = 0;
		for (UserDataInputModel model : fileList) {
			
			model.setId(Long.valueOf(index));
			index++;
			
			if (StringUtils.isEmpty(model.getParam1())){
				model.setParam1(null);
			}
			if (StringUtils.isEmpty(model.getParam2())){
				model.setParam2(null);
			}
			if (StringUtils.isEmpty(model.getParam3())){
				model.setParam3(null);
			}
			if (StringUtils.isEmpty(model.getParam4())){
				model.setParam4(null);
			}
		}
		LogService.info("===微信登记送话费活动===用户数据导入session成功，共有" + fileList.size() + "条数据！！！");
		session.setAttribute(SESSION_LIST_KEY, fileList);
		paramsMap.put("message", "上传缓存成功");
		
		// 用于页面判断是否有session
		session.setAttribute(SESSION_FILE_KEY, "1");
		
	}

	/**
	 * session数据存入数据库
	 * @param paramsMap
	 * @param session
	 * @throws Exception
	 */
	public void uploadRecordToDB(Map<String, Object> paramsMap, HttpSession session) throws Exception {
		
		String operator = (String) paramsMap.get("operator");
		String idss = (String) paramsMap.get("ids");
		String[] ids =  idss.split(",");
		List<UserDataInputModel> fileList = (List<UserDataInputModel>) session.getAttribute(SESSION_LIST_KEY);
		if (fileList != null) {
			for (String id : ids) {
				if ("-1".equals(id)) {
					continue;
				}
				final UserDataInputModel record = fileList.get(Integer.valueOf(id));
				record.setOperator(operator);
				record.setId(null);
				handleTemp(record);
			}
		}
		LogService.info("===微信登记送话费活动===用户数据更新至数据库成功，共更新" + fileList.size() + "条数据！！！");
		session.removeAttribute(SESSION_LIST_KEY);
		
		// TODO 导入数据后检查是否有符合资格未发奖用户, 自动发奖
		// recharge();
		// paramsMap.put("message", "上传数据库成功, 为未发奖用户发奖中... ...");
		
		paramsMap.put("message", "上传数据库成功");
		// 用于页面判断是否有session
		session.setAttribute(SESSION_FILE_KEY, "0");
	}
	
	public void cleanBuf(Map<String, Object> paramsMap, HttpSession session) throws Exception {
		session.removeAttribute(SESSION_LIST_KEY);
		paramsMap.put("message", "清除缓存成功");
		// 用于页面判断是否有session
		session.setAttribute(SESSION_FILE_KEY, "0");
	}
	
	private List getDBList(Integer start, Integer length, PageResponse pageResponse, Map<String, Object> paramsMap) throws AppException{
		
		String phone = (String) paramsMap.get("phone");
		String applyStartTime = (String) paramsMap.get("applyStartTime");
		String applyEndTime = (String) paramsMap.get("applyEndTime");
		String loanStartTime = (String) paramsMap.get("loanStartTime");
		String loanEndTime = (String) paramsMap.get("loanEndTime");
		
		String prd_id = (String) paramsMap.get("prd_id");
		String channel = (String) paramsMap.get("channel"); 
		String activity = (String) paramsMap.get("activity"); 
		
		StringBuilder whereParams = new StringBuilder();
		if (StringUtils.isNotEmpty(phone)) {
			whereParams.append("and phone = '" + phone + "' ");
		}
		if (StringUtils.isNotEmpty(applyStartTime)) {
			whereParams.append("and apply_date >= '" + applyStartTime + "' ");
		}
		if (StringUtils.isNotEmpty(applyEndTime)) {
			whereParams.append("and apply_date <= '" + applyEndTime + "' ");
		}
		if (StringUtils.isNotEmpty(loanStartTime)) {
			whereParams.append("and loan_date >= '" + loanStartTime + "' ");
		}
		if (StringUtils.isNotEmpty(loanEndTime)) {
			whereParams.append("and loan_date <= '" + loanEndTime + "' ");
		}
		if (StringUtils.isNotEmpty(prd_id)) {
			whereParams.append("and prd_id = '" + prd_id + "' ");
		}
		if (StringUtils.isNotEmpty(channel)) {
			whereParams.append("and channel = '" + channel + "' ");
		}
		if (StringUtils.isNotEmpty(activity)) {
			whereParams.append("and activity_name = '" + activity + "' ");
		}
		String sql = "SELECT a.*, b. NAME prd_name, c.channel_name channel_name FROM input_userdata a LEFT JOIN "
				+ "comm_product_prdid b on a.prd_id = b.prdid LEFT JOIN comm_product_channel c on a.channel = c.channel_code "
				+ "where 1=1 ";
		StringBuilder listSql = new StringBuilder(sql);
		listSql.append(whereParams).append("order by ctime desc ");
		String sqlCount = "select count(1) from (" + listSql.toString() + ") z ";
		if (start != null && length != null) {
			listSql.append("limit " + start + ", " + length);
		}
		int count = dao.getInt(sqlCount);
		List<UserDataInputModel> list = dao.list(listSql.toString(), UserDataInputModel.class);
		for (UserDataInputModel model : list) {
			if (StringUtils.isEmpty(model.getChannel_name())) {
				model.setChannel_name(model.getChannel());
			}
			if (StringUtils.isEmpty(model.getPrd_name())) {
				model.setPrd_name(model.getPrd_id());
			}
			
			model.setPhone(PhoneNumNideUtil.hideNum(model.getPhone()));
		}
		
		pageResponse.setITotalRecords(count);
		pageResponse.setITotalDisplayRecords(count);
		return list;
	}
	
	/**
	 * 处理模糊的手机号码
	 * @param string
	 * @return
	 * @throws Exception 
	 */
	private String handleVaguePhone(String activity_id, String phone) throws Exception {

		// 11位手机号码，不确定模糊部分
		StringBuilder sb = new StringBuilder();
		char[] charArray = phone.toCharArray();
		Pattern pattern = Pattern.compile("[0-9]");
		for (Character c : charArray) {
			Matcher isNum = pattern.matcher(c.toString());
			if (isNum.matches()) {
				sb.append(c);
			} else {
				sb.append("_");
			}
		}
		String likePhone = sb.toString();
		
		String getTruePhoneFromRecord = "select mobnum phone from loan_handphone_record where activity_id = ? and mobnum like ? limit 1 ";
		List<UserDataInputModel> list = dao.list(getTruePhoneFromRecord, UserDataInputModel.class, activity_id, likePhone);
		if (list != null && list.size() > 0) {
			return list.get(0).getPhone();
		}
		return StringUtils.replace(sb.toString(), "_", "*");
	}
	
	/**
	 * session数据与数据库数据排重, 实现存储
	 * @param record
	 * @return
	 * @throws Exception
	 */
	private void handleTemp(UserDataInputModel record) throws Exception {
		
		List<UserDataInputModel> dbRecordList = dao
				.list("select * from input_userdata where activity_id = ? and phone = ? and apply_date = ? ",
						UserDataInputModel.class, record.getActivity_id(),
						record.getPhone(), record.getApply_date());
		
		if (null == dbRecordList || dbRecordList.size() == 0) {
			dao.add(record);
		} else {
			boolean isNewRecord = false;
			for (UserDataInputModel dbRecord : dbRecordList) {
				// 数据库有这条记录（三个字段都一样）
				String db_loan_date = dbRecord.getLoan_date();
				String loan_date = record.getLoan_date();
				String db_loan_money = dbRecord.getLoan_money();
				String loan_money = record.getLoan_money();

				if (StringUtils.isEmpty(db_loan_date) && StringUtils.isEmpty(db_loan_money)) {
					// 数据库两个字段为空（覆盖）
					record.setId(dbRecord.getId());
					dao.update(record, "id");
					isNewRecord = false;
					break;

				} else if (StringUtils.isNotEmpty(db_loan_date) && StringUtils.isNotEmpty(db_loan_money)) {
					// 数据库两个字段不为空
					if (compareDate(db_loan_date, loan_date) == 0) {
						// 时间相同
						if (db_loan_money.equals(loan_money)) {
							// 金额相同（覆盖）
							record.setId(dbRecord.getId());
							dao.update(record, "id");
							isNewRecord = false;
							break;
						} else {
							// 新增
							isNewRecord = true;
							continue;
						}
					} else {
						// 新增
						isNewRecord = true;
						continue;
					}

				} else {
					if (StringUtils.isNotEmpty(db_loan_date)) {
						// 时间不空，金额为空
						if (compareDate(db_loan_date, loan_date) == 0) {
							// 时间相同（覆盖）
							record.setId(dbRecord.getId());
							dao.update(record, "id");
							isNewRecord = false;
							break;
						} else {
							// 新增
							isNewRecord = true;
							continue;
						}

					} else {
						// 金额不空，时间为空
						if (db_loan_money.equals(loan_money)) {
							// 金额相同（覆盖）
							record.setId(dbRecord.getId());
							dao.update(record, "id");
							isNewRecord = false;
							break;
						} else {
							// 新增
							isNewRecord = true;
							continue;
						}
					}
				}
			}
			if (isNewRecord) {
				dao.add(record);
			}
		}

	}
	
	/**
	 * 检查 符合资格没发奖, 补发奖励以及短信
	 * @throws Exception 
	 */
	@SuppressWarnings("unused")
	private void recharge() throws Exception {

		String getNeedRechargeRecord = "SELECT a.id id, c. NAME activity_name, a.mobnum mobnum, a.utime utime, "
				+ "MAX(b.apply_date) apply_date, b.prd_id comp, "
				+ "a.amount amount, d.state money_status FROM loan_handphone_record a LEFT JOIN input_userdata b "
				+ "ON a.activity_id = b.activity_id AND a.mobnum = b.phone "
				+ "LEFT JOIN loan_product_info c ON a.activity_id = c.id LEFT JOIN handphone_order d ON a.id = d.orderid "
				+ "WHERE b.apply_date <= a.utime AND a.utime <= DATE_ADD(b.apply_date, INTERVAL 7 DAY) "
				+ "AND (d.state = '9' OR d.state IS NULL) GROUP BY a.id ";
		
		List<UserInfoSetModel> rechargeList = dao.list(getNeedRechargeRecord, UserInfoSetModel.class);
		for (final UserInfoSetModel record : rechargeList) {
			MyTaskExecutor.getInstance(5).execute(new Runnable() {
				@Override
				public void run() {// 进行充值
					LogService.info("===微信登记送话费活动===正在为" + record.getMobnum() + "用户充值话费，金额：" + record.getAmount() + "元，订单号：" + record.getId());
					String result = PhoneOrderUtil.rechargePhoneFee(record.getMobnum(), Integer.parseInt(record.getAmount()), record.getId(), IServiceType.XMILES_MANAGER, "1", "送钱活动导入数据自动充值", null);
					if (JSONUtils.isGoodJson(result)) {
						int r = JSONUtils.parseResult(result);
						JsonObject object = JSONUtils.toJsonObject(result);
						JsonObject e = JSONUtils.toJsonObject(object.get("result"));
						String msg = JSONUtils.checkAsString(e, "msg");
						if (r == 1) {
							LogService.info("===微信登记送话费活动===订单提交成功，请等待充值结果");
							// 短信发送
							String mobile = record.getMobnum();
							String content = "恭喜您已获得" + record.getActivity_name() + "活动的申请返现金奖励！" + record.getAmount() + "元话费已为您充值，具体到账时间以运营商通知为准。";
							String memo = "送钱活动导入数据充值话费成功提示短信";
							String pid = record.getComp();
							send(mobile, content, memo, 1, Integer.parseInt(pid), null, 0);
						} else {
							LogService.info("===微信登记送话费活动===充值失败, " + msg);
						}
					}
				}
			});
		}
	}
	
	/**
	 * 日期比较
	 * @param date1
	 * @param date2
	 * @return d1 < d2  返回 -1 , d1 > d2 返回 1 , d1 = d2 返回 0
	 * @throws ParseException
	 */
	private static int compareDate(String date1,String date2) throws ParseException {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(format.parse(date1));
		Date d1 = calendar.getTime();
		calendar.setTime(format.parse(date2));
		Date d2 = calendar.getTime();
		return d1.compareTo(d2);
	}
	
	/**
	 * 发送短信
	 * @param mobileNum
	 * @param content
	 * @param memo
	 * @param limit
	 * @param product
	 * @param ip
	 * @param code
	 */
	private static void send(String mobileNum, String content, String memo, int limit, int product, String ip, int code) {
		JsonObject obj = new JsonObject();
		obj.add("phead", new JsonObject());
		obj.addProperty("mobileNum", mobileNum);
		obj.addProperty("content", content);
		obj.addProperty("memo", memo);
		obj.addProperty("limit", limit);
		obj.addProperty("product", product);
		obj.addProperty("ip", ip);
		obj.addProperty("code", code);
		ArrayList<BasicNameValuePair> list = new ArrayList<BasicNameValuePair>();
		list.add(new BasicNameValuePair("data", obj.toString()));
		list.add(new BasicNameValuePair("shandle", "0"));
		String response = HttpConnectionUtils.connection(Constant.COMMON_HOST + "/loan_utils/common?funid=1&rd=" + System.currentTimeMillis(), HttpConnectionUtils.POST, list);
		LogService.info("===微信登记送话费活动===导入数据自动充话费短信提醒，send phone:" + mobileNum + ", content:" + content + ", product:" + product + ", response:" + response);
	}
	
	/**
	 * 自定义线程池, 可指定大小
	 * @author Administrator
	 */
	private static class MyTaskExecutor {

		private static int max_pool_size = 5;

		private static MyTaskExecutor sInstance = null;

		private ExecutorService mTaskService;

		private MyTaskExecutor() {
			mTaskService = new ThreadPoolExecutor(max_pool_size, max_pool_size, 0L,
					TimeUnit.MILLISECONDS, new LinkedBlockingQueue<Runnable>(),
					new DefaultThreadFactory());
		}

		public synchronized static MyTaskExecutor getInstance(int max_pool_size) {
			MyTaskExecutor.max_pool_size = max_pool_size;
			if (sInstance == null) {
				sInstance = new MyTaskExecutor();
			}
			return sInstance;
		}

		public void execute(Runnable runnable) {
			mTaskService.execute(runnable);
		}

		private static class DefaultThreadFactory implements ThreadFactory {

			private static final AtomicInteger poolNumber = new AtomicInteger(1);
			private final ThreadGroup group;
			private final AtomicInteger threadNumber = new AtomicInteger(1);
			private final String namePrefix;

			DefaultThreadFactory() {
				SecurityManager s = System.getSecurityManager();
				group = (s != null) ? s.getThreadGroup() : Thread.currentThread()
						.getThreadGroup();
				namePrefix = getClass().getSimpleName() + "-"
						+ poolNumber.getAndIncrement() + "-thread";
			}

			public Thread newThread(Runnable r) {
				Thread t = new Thread(group, r, namePrefix
						+ threadNumber.getAndIncrement(), 0);

				if (t.isDaemon()) {
					t.setDaemon(false);
				}

				if (t.getPriority() != Thread.NORM_PRIORITY) {
					t.setPriority(Thread.NORM_PRIORITY);
				}
				return t;
			}
		}
	}
	
	private static boolean isPhone(String str) {
		if(StringUtils.isEmpty(str)){
			return false;
		}
		Pattern pattern = Pattern.compile("[0-9]{11}\\.{1}0{2}");
		Matcher isNum = pattern.matcher(str);
		if (!isNum.matches()) {
			return false;
		}
		return true;
	}
	
	private static boolean isInteger(String str) {
		if(StringUtils.isEmpty(str)){
			return false;
		}
		Pattern pattern = Pattern.compile("[0-9]+\\.{1}0{2}");// 数字一次或多次, 再接.00
		Matcher isNum = pattern.matcher(str);
		if (!isNum.matches()) {
			return false;
		}
		return true;
	}
	
	private static String patternDate(String str){
		
		if(StringUtils.isEmpty(str)){
			return null;
		}
		
		str = str.trim();
		
		Matcher mat = Pattern.compile("\\d{4}(\\-|\\.|\\/)\\d{1,2}(\\-|\\.|\\/)\\d{1,2}").matcher(str);
		if (mat.matches()) {// 2017-06-01
			return str + " 00:00:00";
		}
		
		Matcher mat2 = Pattern.compile("\\d{4}(\\-|\\.|\\/)\\d{1,2}(\\-|\\.|\\/)\\d{1,2}\\s+\\d{1,2}(\\:)\\d{1,2}(\\:)\\d{1,2}").matcher(str);
		if (mat2.matches()) {
			return str;
		}
		return null;
	}
	
	private static boolean isNumber(String str){
		if(StringUtils.isEmpty(str)){
			return false;
		}
		Pattern pattern = Pattern.compile("\\d+\\.?\\d+");
		Matcher isNum = pattern.matcher(str);
		if (!isNum.matches()) {
			return false;
		} else {
			return true;
		}
	}
	
}
